import os

from flask import Flask, request, send_file
from flask_restx import Resource, Api, reqparse, fields
import json
from flask_sqlalchemy import SQLAlchemy
import json
import math
import sqlite3
import time
from datetime import datetime

from sqlalchemy import text
import matplotlib.pyplot as plt
import io

from datetime import datetime, timezone, timedelta


def to_timestamp(time_str, fmt='%m-%d-%Y %H:%M:%S'):
    time_tuple = time.strptime(time_str, fmt)
    timestamp = time.mktime(time_tuple)
    return timestamp


# 给定一个日期检查是不是周末
def is_weekend(date_str):
    date_obj = datetime.strptime(date_str, '%m-%d-%Y').date()
    # 判断是否为周末
    if date_obj.weekday() in [6]:
        return True
    else:
        return False
    pass


def is_holiday(date_str):

        a = """
        [
  {
    "date": "2023-01-01",
    "localName": "New Year's Day",
    "name": "New Year's Day",
    "countryCode": "AU",
    "fixed": true,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-01-26",
    "localName": "Australia Day",
    "name": "Australia Day",
    "countryCode": "AU",
    "fixed": false,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-03-06",
    "localName": "Labour Day",
    "name": "Labour Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-WA"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-03-13",
    "localName": "Canberra Day",
    "name": "Canberra Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-ACT"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-03-13",
    "localName": "March Public Holiday",
    "name": "March Public Holiday",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-SA"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-03-13",
    "localName": "Eight Hours Day",
    "name": "Eight Hours Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-TAS"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-03-13",
    "localName": "Labour Day",
    "name": "Labour Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-VIC"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-04-07",
    "localName": "Good Friday",
    "name": "Good Friday",
    "countryCode": "AU",
    "fixed": false,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-04-08",
    "localName": "Easter Eve",
    "name": "Holy Saturday",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-ACT",
      "AU-NSW",
      "AU-NT",
      "AU-QLD",
      "AU-SA",
      "AU-VIC"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-04-09",
    "localName": "Easter Sunday",
    "name": "Easter Sunday",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-ACT",
      "AU-NSW",
      "AU-VIC"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-04-10",
    "localName": "Easter Monday",
    "name": "Easter Monday",
    "countryCode": "AU",
    "fixed": false,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-04-25",
    "localName": "Anzac Day",
    "name": "Anzac Day",
    "countryCode": "AU",
    "fixed": true,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-05-01",
    "localName": "May Day",
    "name": "May Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-NT"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-05-01",
    "localName": "Labour Day",
    "name": "Labour Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-QLD"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-05-29",
    "localName": "Reconciliation Day",
    "name": "Reconciliation Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-ACT"
    ],
    "launchYear": 2018,
    "type": "Public"
  },
  {
    "date": "2023-06-05",
    "localName": "Western Australia Day",
    "name": "Western Australia Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-WA"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-06-12",
    "localName": "Queen's Birthday",
    "name": "Queen's Birthday",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-ACT",
      "AU-NSW",
      "AU-NT",
      "AU-SA",
      "AU-TAS",
      "AU-VIC"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-08-07",
    "localName": "Picnic Day",
    "name": "Picnic Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-NT"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-10-02",
    "localName": "Labour Day",
    "name": "Labour Day",
    "countryCode": "AU",
    "fixed": false,
    "global": false,
    "counties": [
      "AU-ACT",
      "AU-NSW",
      "AU-SA"
    ],
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-12-25",
    "localName": "Christmas Day",
    "name": "Christmas Day",
    "countryCode": "AU",
    "fixed": false,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  },
  {
    "date": "2023-12-26",
    "localName": "Boxing Day",
    "name": "St. Stephen's Day",
    "countryCode": "AU",
    "fixed": false,
    "global": true,
    "counties": null,
    "launchYear": null,
    "type": "Public"
  }
]
        """
        # print(a)
        result = False
        data = json.loads(a.strip())
        print(data)
        date_obj = datetime.strptime(date_str, '%m-%d-%Y').date()
        s = date_obj.strftime('%Y-%m-%d')

        for item in data:
            if s == item.get('date'):
                result = item.get('name')
        return result


def getDate():
    # 获取当前 UTC 时间对象
    utc_time = datetime.utcnow()
    # 创建澳洲时区的 timezone 对象
    au = timezone(timedelta(hours=10))
    # 将 UTC 时间对象转换为澳洲时区时间对象
    return utc_time.replace(tzinfo=timezone.utc).astimezone(au)


app = Flask(__name__)
# 配置文档展开
# app.config.SWAGGER_UI_DOC_EXPANSION = 'full'
api = Api(app)
# 数据库地址配置
app.config[
    "SQLALCHEMY_DATABASE_URI"] = f"sqlite:///{os.path.dirname(os.path.abspath(__file__)) + os.path.sep + 'main.db'}"
db = SQLAlchemy()
db.init_app(app)


class EventModel(db.Model):
    __tablename__ = 'events'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    date = db.Column(db.String(128))
    _date = db.Column(db.String(128))
    from_time = db.Column(db.Integer, name='from')
    to_time = db.Column(db.Integer, name='to')
    location = db.Column(db.String(256))
    description = db.Column(db.String(512))
    last_update = db.Column(db.DateTime, name='last-update', default=datetime.utcnow, onupdate=getDate)


with app.app_context():
    db.create_all()


class EventDao:
    def __init__(self, db):

        self.db = db
        pass

    def create(self, object={}):
        try:
            # 检查日期时间是否冲突
            if self.__checkDateCross(object):
                raise Exception("Time conflict")
            event = EventModel()
            event.name = object.get('name')
            event.date = object.get('date')
            event._date = int(to_timestamp(object.get('date') + " 00:00:00"))
            event.from_time = to_timestamp(object.get('date') + " " + object.get("from"))
            event.to_time = to_timestamp(object.get('date') + " " + object.get("to"))
            event.description = object.get('description')
            event.location = json.dumps(object.get('location'))
            self.db.session.add(event)
            self.db.session.commit()
            # 转换成年月日时分秒
            datetime_obj = datetime.strptime(str(event.last_update), '%Y-%m-%d %H:%M:%S.%f')
            formatted_date = datetime_obj.strftime('%Y-%m-%d %H:%M:%S')
            return {
                "code": 200,
                "data": {
                    "id": str(event.id),
                    "last_update": formatted_date,
                    "_links": {
                        "self": {
                            "href": "/events/" + str(event.id)
                        }
                    }
                }
            }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def delete(self, id):
        try:
            event = self.db.session.get(EventModel, id)
            if event:
                self.db.session.delete(event)
                self.db.session.commit()
                return {
                    "code": 200,
                    "data": {
                        "id": id,
                        "message": f"The event with id {id} was removed from the database!"
                    }
                }
            pass
            raise Exception("no record")
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def update(self, id, data):
        try:
            # 检查记录是否存在
            event = self.db.session.get(EventModel, id)
            # 记录不存在报错
            if not event:
                raise Exception("no record")
            # 获取JSON过来的数据
            data['id'] = id
            data['date'] = event.date
            if self.__checkDateCross1(data):
                raise Exception("Time conflict")
            event.from_time = to_timestamp(event.date + " " + data.get("from"))
            event.to_time = to_timestamp(event.date + " " + data.get("to"))
            event.description = data.get('description')
            event.verified = True
            self.db.session.commit()
            # 转换成年月日时分秒
            datetime_obj = datetime.strptime(str(event.last_update), '%Y-%m-%d %H:%M:%S.%f')
            formatted_date = datetime_obj.strftime('%Y-%m-%d %H:%M:%S')
            return {
                "code": 200,
                "data": {
                    "id": str(event.id),
                    "last_update": formatted_date,
                    "_links": {
                        "self": {
                            "href": "/events/" + str(event.id)
                        }
                    }
                }
            }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def get(self, id):
        try:
            # 根据id查询数据表记录
            stmt = self.db.select(EventModel).where(EventModel.id == id)
            result = self.db.session.execute(stmt).scalar_one_or_none()
            if result:
                datetime_obj = datetime.strptime(str(result.last_update), '%Y-%m-%d %H:%M:%S.%f')
                formatted_date = datetime_obj.strftime('%Y-%m-%d %H:%M:%S')
                response_data = {
                    "id": result.id,
                    "last-update": formatted_date,
                    "name": result.name,
                    "date": result.date,
                    "from": datetime.fromtimestamp(result.from_time).strftime("%H:%M:%S"),
                    "to": datetime.fromtimestamp(result.to_time).strftime("%H:%M:%S"),
                    "location": json.loads(result.location),
                    "_metadata": {
                        "wind-speed": "",
                        "weather": "",
                        "humidity": "",
                        "temperature": "",
                        "holiday": is_holiday(result.date),
                        "weekend": is_weekend(result.date)
                    },
                    "_links": {
                        "self": {
                            "href": "/events/" + str(id)
                        },
                        "previous": {
                            "href": "/events/" + self.__getPreData(id) if self.__getPreData(id) else "null"
                        },
                        "next": {
                            "href": "/events/" + self.__getNextData(id) if self.__getNextData(id) else "null"
                        }
                    }
                }
                return response_data
            else:
                raise Exception("No Data")
        except Exception as e:
            return False
            pass

    def filter(self, data):
        data.order = str(data.order).replace(" ", "+")
        try:
            start = (int(data.page) - 1) * int(data.size)
            sort = 'asc'
            if str(data.order).startswith("+"):
                sort = "asc"
            if str(data.order).startswith("-"):
                sort = "desc"
            print("sort=" + sort)
            column = str(data.order).replace("+", "").replace("-", "")
            # 计算总记录数
            total_sql = f"select {data.filter}  from events order by {column} {sort}"
            total_result = self.db.session.execute(text(total_sql)).all()
            total_page = math.ceil(len(total_result) / int(data.size))
            sql = f"select {data.filter}  from events order by {column} {sort} limit {start},{data.size}"

            result = self.db.session.execute(text(sql)).all()

            arr = []
            for item in result:
                arr.append(item._asdict())

            return {
                "code": 200,
                "data": {
                    "page": data.page,
                    "page-size": data.size,
                    "events": arr,
                    "_links": {
                        "self": f"/events/order={data.order}&page={data.page}&size={data.size}&filter={str(data.filter)}",
                        "next": f"/events/order={data.order}&page={int(data.page) + 1}&size={data.size}&filter={str(data.filter)}" if int(
                            total_page) > int(data.page) else "null"
                    }
                }
            }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
            pass

    def statistics(self, data):
        try:
            # 判断提交参数
            if data.format not in ['json', 'image']:
                raise Exception("format must  be json or  image")
            if data.format == 'json':
                return {
                    "code": 200,
                    "data": {
                        "total": self.__getTotalCount(),
                        "pre-days": self.__getDayCount(),
                        "total-current-week": self.__getCurrentWeek(),
                        "total-current-month": self.__getCurrentMonth(),
                    }
                }
                pass
            if data.format == 'image':
                data = {
                    "total": self.__getTotalCount(),
                    "pre-days": self.__getDayCount(),
                    "total-current-week": self.__getCurrentWeek(),
                    "total-current-month": self.__getCurrentMonth()
                }
                return {
                    "code": 200,
                    "data": self.__getImageData(data)
                }
        except Exception as e:
            return {
                "code": 400,
                "message": str(e)
            }
        pass

    def __getImageData(self, data):
        # Get the x and y values for the bar chart
        labels = list(data["pre-days"].keys())
        values = list(data["pre-days"].values())

        # Create the bar chart
        fig, ax = plt.subplots()
        ax.bar(labels, values)

        # Set the chart title and axis labels
        ax.set_title(
            f"total={data.get('total')},total-current-week={data.get('total-current-week')},total-current-month={data.get('total-current-month')}")
        ax.set_xlabel("Date")
        ax.set_ylabel("Number of events")

        # Save the chart as a PNG in memory
        buffer = io.BytesIO()
        fig.savefig(buffer, format="png")
        buffer.seek(0)
        return buffer
        pass

    def __getCurrentWeek(self):
        now = datetime.now()
        day = now.strftime("%Y-%m-%d")
        today = datetime.today()
        weekday = today.weekday() + 1
        print(day)
        start_date = int(to_timestamp(day + " 00:00:00", fmt="%Y-%m-%d %H:%M:%S"))
        end_date = start_date + (3600 * 24 * (7 - weekday))
        sql = f"select count(*) as count  from events  where _date>={start_date} and _date<={end_date}"
        resutl = self.db.session.execute(text(sql)).all()
        if len(resutl) == 0:
            return 0
        print(resutl[0].count)
        return resutl[0].count
        pass

    def __getCurrentMonth(self):
        now = datetime.now()
        year_month = now.strftime("%Y-%m")
        sql = f"SELECT strftime('%Y-%m', datetime(_date, 'unixepoch')) AS month, COUNT(*) AS count FROM events where month='{year_month}' GROUP BY 1 ORDER BY 1"
        print(sql)
        resutl = self.db.session.execute(text(sql)).all()
        print(resutl)
        if len(resutl) == 0:
            return 0
        print(resutl[0].count)
        return resutl[0].count
        pass

    def __getTotalCount(self):
        sql = "SELECT COUNT(*)  as count FROM events"
        resutl = self.db.session.execute(text(sql)).all()
        if len(resutl) == 0:
            return 0
        return resutl[0].count
        pass

    def __getDayCount(self):
        sql = "SELECT date, COUNT(*) as count FROM events GROUP BY date ORDER BY date"
        resutl = self.db.session.execute(text(sql)).all()
        data = {}
        for item in resutl:
            data[item.date] = item.count
        return data
        pass

    # 获取上一条数据
    def __getPreData(self, id):
        # 原生查询语句select *  from  events where id<27 ORDER BY id desc  LIMIT 1
        stmt = self.db.select(EventModel).filter(EventModel.id < id).order_by(EventModel.id.desc()).limit(1)
        result = self.db.session.execute(stmt).scalar_one_or_none()
        if result:
            return str(result.id)
        else:
            return None

    # 获取下一条数据
    def __getNextData(self, id):
        stmt = self.db.select(EventModel).filter(EventModel.id > id).order_by(EventModel.id.asc()).limit(1)
        result = self.db.session.execute(stmt).scalar_one_or_none()
        if result:
            return str(result.id)
        else:
            return None
        return

    # 日期冲突检
    def __checkDateCross(self, obj={}):
        from_time = int(to_timestamp(obj.get('date') + " " + obj.get("from")))
        to_time = int(to_timestamp(obj.get('date') + " " + obj.get("to")))
        # 开始时间不能大于结束时间
        if from_time > to_time:
            return False

        sql = f"SELECT *  FROM events WHERE date='{obj.get('date')}'"
        sql = sql + f" and ( `from` == {from_time} AND `to` == {to_time} ) OR ( `from` > {from_time} AND `to` < {to_time} ) OR ( `from` < {from_time} AND `to` > {to_time} ) OR ( `to` > {from_time} AND `to` <{to_time} )"

        result = self.db.session.execute(text(sql)).all()

        if len(result) > 0:
            return True
        else:
            return False
        pass

    # 日期冲突检测排除自己
    def __checkDateCross1(self, obj={}):
        from_time = int(to_timestamp(obj.get('date') + " " + obj.get("from")))
        to_time = int(to_timestamp(obj.get('date') + " " + obj.get("to")))
        # 开始时间不能大于结束时间
        if from_time > to_time:
            return False
        sql = f"SELECT * FROM events WHERE date='{obj.get('date')}'"
        sql = sql + f" and id!={obj.get('id')} and ( `from` == {from_time} AND `to` == {to_time} ) and ( `from` > {from_time} AND `to` < {to_time} ) OR ( `from` < {from_time} AND `to` >= {to_time} ) OR ( `to` > {from_time} AND `to` <{to_time} )"

        result = self.db.session.execute(text(sql)).all()

        if len(result) > 0:
            return True
        else:
            return False
        pass


# 数据库操作类
eventDao = EventDao(db)

# 定义请求数据模型
my_model = api.model('MyModel', {
    "name": fields.String(description="The name of the event", required=True),
    "date": fields.String(description="The date of the event (in DD-MM-YYYY format)", required=True),
    "from": fields.String(description="The start time of the event (in HH:MM:SS format)", required=True),
    "to": fields.String(description="The end time of the event (in HH:MM:SS format)", required=True),
    "location": fields.String(description="The location of the event ", required=True),
    # "location": fields.Nested({
    #     "street": fields.String(description="The street name"),
    #     "suburb": fields.String(description="The suburb name"),
    #     "state": fields.String(description="The state abbreviation (e.g. NSW)"),
    #     "post-code": fields.String(description="The post code")
    # }),
    "description": fields.String(description="A description of the event")
})

event_model = api.model('Event', {
    'from': fields.String(required=True, description='The start time of the event in HH:MM:SS format'),
    'to': fields.String(required=True, description='The end time of the event in HH:MM:SS format'),
    'description': fields.String(description='Optional. A description of the event')
})


# 创建数据
@api.route("/events", methods=['POST', 'GET'])
class EventsHandler1(Resource):
    @api.doc(responses={200: 'Events returned successfully', 400: 'Invalid input'},
             params={
                 'order': {'type': 'string',
                           'description': 'The order in which to return events (e.g. "+id" or "-name")',
                           'default': '+id'},
                 'page': {'type': 'integer', 'description': 'The page number to return', 'default': 1},
                 'size': {'type': 'integer', 'description': 'The number of events to return per page', 'default': 10},
                 'filter': {'type': 'string',
                            'description': 'The fields to include in each event object (comma-separated)',
                            'default': 'id,name'}
             })
    def get(self):
        """
        Get a list of events.
        Parameters:
            order (str): The order in which to return events (e.g. "+id" or "-name"). Default is "+id".
            page (int): The page number to return. Default is 1.
            size (int): The number of events to return per page. Default is 10.
            filter (str): The fields to include in each event object (comma-separated). Default is "id,name".
        """
        # 获取JSON传递过来的数据
        parser = reqparse.RequestParser()
        parser.add_argument('order')
        parser.add_argument('page')
        parser.add_argument('size')
        parser.add_argument('filter')
        args = parser.parse_args()
        if not args.get('order'):
            args['order'] = '+id'
            pass
        if not args.get('page'):
            args['page'] = 1
            pass
        if not args.get('size'):
            args['size'] = 10
            pass
        if not args.get('filter'):
            args['filter'] = 'id,name'
            pass

        # 解析JSON转成字典数据
        result = eventDao.filter(args)
        if result.get('code') == 200:
            return result.get('data'), 200
        else:
            return {"message": result.get('message')}, 400
        pass

    @api.doc(responses={201: 'Event created successfully', 400: 'Invalid input'})
    @api.expect(my_model)
    def post(self):
        """
        Create a new event.

        Request Body:
        {
            "name": "my birthday paty",
            "date": "02-01-2020",
            "from": "13:09:00",
            "to": "13:08:00",
            "location": {
                "street": "2158 Night Ave",
                "suburb": "Kensington",
                "state": "NSW",
                "post-code": "2033"
            },
            "description": "some notes on the vent"
        }
        """
        # 获取JSON传递过来的数据
        data = request.get_data()
        # 解析JSON转成字典数据
        json_data = json.loads(data.decode("UTF-8"))
        result = eventDao.create(json_data)
        if result.get('code') == 200:
            return result.get('data'), 201
        else:
            return {"message": result.get('message')}, 400
        pass


# 查询数据
@api.route("/events/<int:id>", methods=['GET', 'DELETE', 'PATCH'])
class EventsHandler2(Resource):
    def get(self, id):
        result = eventDao.get(id)
        if result:
            return result, 200
        else:
            return {"message": "data created fail"}, 400
        pass

    def delete(self, id):
        result = eventDao.delete(id)
        if result.get('code') == 200:
            return result.get('data'), 201
        else:
            return {"message": result.get('message')}, 400
        pass

    @api.expect(event_model)
    def patch(self, id):
        """
        Update an event by ID.

        Parameters:
            id (int): The ID of the event to update.

        Request Body:
            from (str): The start time of the event in HH:MM:SS format.
            to (str): The end time of the event in HH:MM:SS format.
            description (str): Optional. A description of the event.

        Returns:
            dict: A dictionary containing the updated event data.
        """
        # 获取JSON传递过来的数据
        data = request.get_data()
        # 解析JSON转成字典数据
        json_data = json.loads(data.decode("UTF-8"))

        result = eventDao.update(id, data=json_data)
        if result.get('code') == 200:
            return result.get('data'), 200
        else:
            return {"message": result.get('message')}, 400
        pass


# 统计数据
@api.route("/events/statistics", methods=['GET'])
class EventsHandler3(Resource):

    @api.doc(params={
        'format': 'Optional. The response format. If set to "image", returns a PNG image of the statistics chart.'
    })
    def get(self):
        """
        Returns statistics for events.
        Parameters:
            format (str): Optional. The response format. If set to 'image', returns a PNG image of the statistics chart.
        Returns:
            dict or file: A dictionary containing the statistics data, or a PNG image file if format is set to 'image'.
        """
        parser = reqparse.RequestParser()
        parser.add_argument('format', type=str)
        args = parser.parse_args()
        print(args)
        result = eventDao.statistics(data=args)
        if result.get('code') == 200:
            if args.format == 'image':
                return send_file(result.get("data"), mimetype='image/png')
            else:
                return result.get('data'), 200
        else:
            return {"message": result.get('message')}, 400
        pass


if __name__ == '__main__':
    app.run()
